﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CsvToSqlite
{
    /// <summary>
    /// 处理结果输出信息的数据传输对象
    /// </summary>
    public class ProcessingOutputDto
    {
        /// <summary>
        /// 处理是否成功
        /// </summary>
        public bool IsSuccess { get; set; }

        /// <summary>
        /// 处理过程中的错误信息，如果处理成功则为 null
        /// </summary>
        public string ErrorMessage { get; set; }

        /// <summary>
        /// 处理的记录数
        /// </summary>
        public int ProcessedRecordCount { get; set; }

        /// <summary>
        /// 其他额外的处理信息，例如日志等
        /// </summary>
        public Dictionary<string, string> AdditionalInfo { get; set; }
    }

    /// <summary>
    /// 处理结果输出接口
    /// </summary>
    public interface IProcessingOutput
    {
        /// <summary>
        /// 输出处理结果
        /// </summary>
        /// <param name="outputDto">处理结果输出信息的数据传输对象</param>
        void OutputResult(ProcessingOutputDto outputDto);
    }

    /// <summary>
    /// CSV数据导入实现类
    /// </summary>
    public class CsvDataImporter : ICsvDataImporter
    {
        private char _delimiter = ',';
        private bool _hasHeader = true;
        private string _encoding = "utf-8";
        private Dictionary<string, string> _columnMappings = new Dictionary<string, string>();
        private string _tableName;
        private string _primaryKey;
        private IProcessingOutput _output;

        public CsvDataImporter(IProcessingOutput output)
        {
            _output = output;
        }

        public void ConfigureCsvFormat(char delimiter = ',', bool hasHeader = true, string encoding = "utf-8")
        {
            _delimiter = delimiter;
            _hasHeader = hasHeader;
            _encoding = encoding;
        }

        public void SetColumnMappings(Dictionary<string, string> columnMappings)
        {
            _columnMappings = columnMappings ?? throw new ArgumentNullException(nameof(columnMappings));
        }

        public void SetTargetTable(string tableName, string primaryKey)
        {
            _tableName = tableName ?? throw new ArgumentNullException(nameof(tableName));
            _primaryKey = primaryKey ?? throw new ArgumentNullException(nameof(primaryKey));
        }

        public async Task<int> ImportAsync(string csvFilePath, string dbFilePath)
        {
            var outputDto = new ProcessingOutputDto
            {
                IsSuccess = false,
                ProcessedRecordCount = 0,
                AdditionalInfo = new Dictionary<string, string>()
            };

            try
            {
                if (!File.Exists(csvFilePath))
                {
                    var errorMessage = $"CSV文件不存在: {csvFilePath}";
                    throw new FileNotFoundException(errorMessage, csvFilePath);
                }

                var encoding = Encoding.GetEncoding(_encoding);
                int importedCount = 0;

                using (var connection = new SQLiteConnection($"Data Source={dbFilePath};Version=3;"))
                {
                    await connection.OpenAsync();

                    // 创建表
                    await CreateTableAsync(connection);

                    // 分批导入数据
                    const int batchSize = 1000;
                    var currentBatch = new List<string[]>();

                    using (var reader = new StreamReader(csvFilePath, encoding))
                    {
                        // 读取表头
                        string[] headers = null;
                        if (_hasHeader)
                        {
                            var headerLine = await reader.ReadLineAsync();
                            headers = headerLine.Split(_delimiter);
                        }

                        string line;
                        while ((line = await reader.ReadLineAsync()) != null)
                        {
                            var values = line.Split(_delimiter);
                            currentBatch.Add(values);

                            if (currentBatch.Count >= batchSize)
                            {
                                importedCount += await InsertBatchAsync(connection, currentBatch, headers);
                                currentBatch.Clear();
                            }
                        }

                        // 导入最后一批
                        if (currentBatch.Any())
                        {
                            importedCount += await InsertBatchAsync(connection, currentBatch, headers);
                        }
                    }
                }

                outputDto.IsSuccess = true;
                outputDto.ProcessedRecordCount = importedCount;
                outputDto.AdditionalInfo["Message"] = "数据导入成功";
            }
            catch (Exception ex)
            {
                outputDto.ErrorMessage = ex.Message;
                outputDto.AdditionalInfo["StackTrace"] = ex.StackTrace;
            }
            finally
            {
                _output.OutputResult(outputDto);
            }

            return outputDto.ProcessedRecordCount;
        }

        private async Task CreateTableAsync(SQLiteConnection connection)
        {
            var columnDefinitions = _columnMappings.Values
                .Select((col, index) =>
                    $"{EscapeSqlName(col)} TEXT{(col == _primaryKey ? " PRIMARY KEY" : "")}")
                .ToList();

            var createTableSql = $"CREATE TABLE IF NOT EXISTS {EscapeSqlName(_tableName)} ({string.Join(", ", columnDefinitions)})";

            using (var command = new SQLiteCommand(createTableSql, connection))
            {
                await command.ExecuteNonQueryAsync();
            }
        }

        private async Task<int> InsertBatchAsync(SQLiteConnection connection, List<string[]> batch, string[] headers)
        {
            var insertColumns = _columnMappings.Values.Select(EscapeSqlName).ToList();
            var insertSql = $"INSERT INTO {EscapeSqlName(_tableName)} ({string.Join(", ", insertColumns)}) VALUES ";

            using (var transaction = connection.BeginTransaction())
            {
                foreach (var values in batch)
                {
                    var valueList = new List<string>();
                    foreach (var csvColumnName in _columnMappings.Keys)
                    {
                        var columnIndex = headers?.ToList().IndexOf(csvColumnName) ??
                                         _columnMappings.Keys.ToList().IndexOf(csvColumnName);

                        if (columnIndex >= 0 && columnIndex < values.Length)
                        {
                            valueList.Add($"\"{EscapeSqlValue(values[columnIndex])}\"");
                        }
                        else
                        {
                            valueList.Add("NULL");
                        }
                    }

                    var rowSql = $"({string.Join(", ", valueList)})";
                    using (var command = new SQLiteCommand(insertSql + rowSql, connection))
                    {
                        await command.ExecuteNonQueryAsync();
                    }
                }

                transaction.Commit();
            }

            return batch.Count;
        }

        private string EscapeSqlName(string name)
        {
            return $"\"{name.Replace("\"", "\"\"")}\"";
        }

        private string EscapeSqlValue(string value)
        {
            return value?.Replace("\"", "\"\"") ?? string.Empty;
        }
    }
}